package com.swpu.project.util;

import java.io.*;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.util.*;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.swpu.project.constant.XlxsConstant;
import com.swpu.project.model.entity.*;
import com.swpu.project.service.*;
import com.swpu.project.service.impl.DictionaryContentServiceImpl;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.*;

import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Consumer;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Component
public class XlxsSwithcUtil {

    @Resource
    IDataDictionaryService dictionaryName;
    @Resource
    IDictionaryContentService dictionaryContent;
    @Resource
    INativeService iNativeService;
    @Resource
    ISecondaryService iSecondaryService;
    @Resource
    IHeavyMetalService iHeavyMetalService;
    @Resource
    INonHeavyMetalService iNonHeavyMetalService;
    @Resource
    IMineralService iMineralService;


    private static List<InformClassify> queryClassifyBackUp;
    private static final XlxsEntity forDetailName = new XlxsEntity();


    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public void inputXlxs(byte[] excelData, XlxsDataType dataType) throws IOException, ParseException {
        try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(excelData);
             XSSFWorkbook xssfWorkbook = new XSSFWorkbook(byteArrayInputStream)) {

            // 获取工作表
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

            // 获取实际的行数，不包括空行
            int totalRows = sheet.getPhysicalNumberOfRows();
            XSSFRow row = sheet.getRow(0);
            int index = Native.returnNum() + 1;

            switch (dataType) {
                case NATIVE:
                    inputNative(sheet, row, index, totalRows);
                    break;
                case SECONDARY:
                    inputSecondary(sheet, row, index, totalRows);
                    break;
            }
        }
    }

    public byte[] outputXlxs(List<XlxsEntity> xlxsEntities) {
        // 创建一个工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("用户");
        // 创建行
        XSSFRow row = sheet.createRow(0);
        List<InformClassify> informClassifies = queryFromDictionary();
        int size = creatfirstRowConstant(row);
        createFirstRow(size, informClassifies, row);

        for (int i = 0; i < xlxsEntities.size(); i++) {
            XSSFRow rows = sheet.createRow(i + 1);
            myCreatCell(rows, xlxsEntities.get(i));
        }

        try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
            xssfWorkbook.write(byteArrayOutputStream);
            return byteArrayOutputStream.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }


    /* public static List<User> ImputXlxs1(String path) throws IOException, ParseException {
        List<User> userList = new ArrayList<>();

        // 1. 获取工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
        // 2. 获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        // 获取实际的行数，不包括空行
        int totalRows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < totalRows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                ArrayList<String> list = new ArrayList<>();
                for (Cell cell : row) {
                    if (cell != null) {
                        //  cell.setCellType(CellType.STRING);
                        DataFormatter dataFormatter = new DataFormatter();
                        String value = dataFormatter.formatCellValue(cell);
                        //  String value = cell.getStringCellValue();// 读取数据
                        System.out.println("单元格值: '" + value + "'");

                        // 检查单元格值是否为空
                        if (value != null && !value.trim().isEmpty()) {
                            list.add(value);
                        }
                    }
                }

                // 检查是否有足够的列数
            *//*    if (list.size() >= 6)
                    String dateString = list.get(4);
                    String dateString2 = list.get(5);

                    // 处理 Excel 序列数转换为日期
                    double excelDate1 = Double.parseDouble(dateString);
                    double excelDate2 = Double.parseDouble(dateString2);

                    // 转换 Excel 日期为 Java 日期
                    Date parse = convertExcelDateToJavaDate(excelDate1);
                    Date parse2 = convertExcelDateToJavaDate(excelDate2)*//*
                ;

                // 创建 User 对象并添加到列表
                User user = new User(Long.parseLong(list.get(0)), list.get(1), Integer.parseInt(list.get(2)), list.get(3), Double.parseDouble(list.get(4)));
                userList.add(user);

            }
        }
        return userList;
    }*/
   @Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRED)
    public void inputXlxsForTest(String path,XlxsDataType dataType) throws IOException, ParseException {


        // 1. 获取工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
        // 2. 获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        // 获取实际的行数，不包括空行
        int totalRows = sheet.getPhysicalNumberOfRows();
        XSSFRow row = sheet.getRow(0);
        int index = Native.returnNum() + 1;
         switch (dataType)
         {
             case NATIVE:
             inputNative(sheet,row,index,totalRows);
             break;
             case SECONDARY:
             inputSecondary(sheet,row,index,totalRows);
                break;
         }


    }
    public void outputXlxsForTest(List<XlxsEntity> xlxsEntities, String path) {
        // 创建一个工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("用户");
        // 创建行
        XSSFRow row = sheet.createRow(0);
        List<InformClassify> informClassifies = queryFromDictionary();
        int size = creatfirstRowConstant(row);
        createFirstRow(size, informClassifies, row);


        for (int i = 0; i < xlxsEntities.size(); i++) {
            XSSFRow rows = sheet.createRow(i + 1);

            myCreatCell(rows, xlxsEntities.get(i));


        }

        try (FileOutputStream fileOutputStream = new FileOutputStream(path);) {
            xssfWorkbook.write(fileOutputStream);



        } catch (IOException e) {
            e.printStackTrace();
        }


    }




    //@Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRED)

  public void inputSecondary(XSSFSheet sheet, XSSFRow row, int index, int totalRows) {

        for (int i = 1; i < totalRows; i++) {
            Secondary secondary = new Secondary();
            Field[] declaredFields = Secondary.class.getDeclaredFields();
            int columns = 1;
            row = sheet.getRow(i);


            for (; columns <= Native.returnNum(); columns++) {
                XSSFCell cell = row.getCell(columns);
                if (cell == null)
                    continue;
                //  cell.setCellType(CellType.STRING);

                DataFormatter dataFormatter = new DataFormatter();
                String value = dataFormatter.formatCellValue(cell);
                //  String value = cell.getStringCellValue();// 读取数据
                System.out.println("单元格值: '" + value + "'");

                // 检查单元格值是否为空
                if (value != null && !value.trim().isEmpty()) {
                    Field declaredField = declaredFields[columns - 1];
                    declaredField.setAccessible(true);
                    try {
                        declaredField.set(secondary, value);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }


            }

            // 检查是否有足够的列数
            /*    if (list.size() >= 6)
                    String dateString = list.get(4);
                    String dateString2 = list.get(5);

                    // 处理 Excel 序列数转换为日期
                    double excelDate1 = Double.parseDouble(dateString);
                    double excelDate2 = Double.parseDouble(dateString2);

                    // 转换 Excel 日期为 Java 日期
                    Date parse = convertExcelDateToJavaDate(excelDate1);
                    Date parse2 = convertExcelDateToJavaDate(excelDate2)*/
            ;

            // 创建 User 对象并添加到列表


            String id = secondary.getId();
            iSecondaryService.save(secondary);



            List<InformClassify> informClassifies = queryFromDictionary();

            for (int z = 0; z < informClassifies.size(); z++) {
                InformClassify informClassify = informClassifies.get(z);
                String name = informClassify.getName();
                Object o = informClassify.initEntity();
                Class<?> entityClass = informClassify.getEntityClass();

                switch (name) {
                    case XlxsConstant.INFORM_HEAVY:
                        o = (HeavyMetal) o;
                        break;
                    case XlxsConstant.INFORM_NON_HEAVY:
                        o = (NonHeavyMetal) o;
                        break;
                    case XlxsConstant.INFORM_MINERAL:
                        o = (Mineral) o;
                        break;

                }


                for (int m = 0; m < informClassify.getSize(); m++, columns++) {
                    XSSFCell cell = row.getCell(columns);
                    Field[] declaredFields1 = entityClass.getDeclaredFields();
                    if (cell == null)
                        continue;


                    DataFormatter dataFormatter = new DataFormatter();
                    String value = dataFormatter.formatCellValue(cell);
                    String informName = informClassify.getColums().get(m);
                    try {
                        Field fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_BELONG);
                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, id);
                        fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_NAME);
                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, informName);

                        fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_DATA);

                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, value);


                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (StrUtil.isBlank(value) || value.equals("0"))
                        continue;

                    System.out.println("单元格值: '" + value + "'");
                    switch (name) {
                        case XlxsConstant.INFORM_HEAVY:

                            iHeavyMetalService.save((HeavyMetal) o);
                            break;
                        case XlxsConstant.INFORM_NON_HEAVY:
                            iNonHeavyMetalService.save((NonHeavyMetal) o);
                            break;
                        case XlxsConstant.INFORM_MINERAL:
                            iMineralService.save((Mineral) o);
                            break;

                    }

                }


            }


        }




    }
    //@Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRED)
    public void inputNative(XSSFSheet sheet, XSSFRow row, int index,int totalRows) {
        for (int i = 1; i < totalRows; i++) {
            Native aNative = new Native();
            Field[] declaredFields = Native.class.getDeclaredFields();
            int columns = 1;
            row = sheet.getRow(i);


            for (; columns <= Native.returnNum(); columns++) {
                XSSFCell cell = row.getCell(columns);
                if (cell == null)
                    continue;


                DataFormatter dataFormatter = new DataFormatter();
                String value = dataFormatter.formatCellValue(cell);


                // 检查单元格值是否为空
                if (value != null && !value.trim().isEmpty()) {
                    Field declaredField = declaredFields[columns - 1];
                    declaredField.setAccessible(true);
                    try {
                        declaredField.set(aNative, value);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }


            }




            String id = aNative.getId();
            iNativeService.save(aNative);
           int fa=1/0;
            List<InformClassify> informClassifies = queryFromDictionary();

            for (int z = 0; z < informClassifies.size(); z++) {
                InformClassify informClassify = informClassifies.get(z);
                String name = informClassify.getName();
                Object o = informClassify.initEntity();
                Class<?> entityClass = informClassify.getEntityClass();

                switch (name) {
                    case XlxsConstant.INFORM_HEAVY:
                        o = (HeavyMetal) o;
                        break;
                    case XlxsConstant.INFORM_NON_HEAVY:
                        o = (NonHeavyMetal) o;
                        break;
                    case XlxsConstant.INFORM_MINERAL:
                        o = (Mineral) o;
                        break;

                }


                for (int m = 0; m < informClassify.getSize(); m++, columns++) {
                    XSSFCell cell = row.getCell(columns);
                    Field[] declaredFields1 = entityClass.getDeclaredFields();
                    if (cell == null)
                        continue;


                    DataFormatter dataFormatter = new DataFormatter();
                    String value = dataFormatter.formatCellValue(cell);
                    String informName = informClassify.getColums().get(m);
                    try {
                        Field fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_BELONG);
                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, id);
                        fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_NAME);
                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, informName);

                        fieldForEntity = entityClass.getDeclaredField(XlxsConstant.INFORM_ENRTITY_FIELD_DATA);

                        fieldForEntity.setAccessible(true);
                        fieldForEntity.set(o, value);


                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    if (StrUtil.isBlank(value) || value.equals("0"))
                        continue;

                    System.out.println("单元格值: '" + value + "'");
                    switch (name) {
                        case XlxsConstant.INFORM_HEAVY:

                            iHeavyMetalService.save((HeavyMetal) o);
                            break;
                        case XlxsConstant.INFORM_NON_HEAVY:
                            iNonHeavyMetalService.save((NonHeavyMetal) o);
                            break;
                        case XlxsConstant.INFORM_MINERAL:
                            iMineralService.save((Mineral) o);
                            break;

                    }

                }


            }


        }












    }


    private void myCreatCell(XSSFRow rows, XlxsEntity xlxsEntity) {
        int i = 0;
        Class<XlxsEntity> xlxsEntityClass = XlxsEntity.class;
        Field[] declaredFields = xlxsEntityClass.getDeclaredFields();
        for (i = 0; i < declaredFields.length - XlxsEntity.getInformNum(); i++) {
            declaredFields[i].setAccessible(true);
            try {
                String s = (String) (declaredFields[i].get(xlxsEntity));
                rows.createCell(i).setCellValue((String) declaredFields[i].get(xlxsEntity));
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }

        }

   /*     rows.createCell(i++).setCellValue(xlxsEntity.getType());
        rows.createCell(i++).setCellValue(xlxsEntity.getId());
        rows.createCell(i++).setCellValue(xlxsEntity.getName());
        rows.createCell(i++).setCellValue(xlxsEntity.getBlockName());
        rows.createCell(i++).setCellValue(xlxsEntity.getOilwellName());
        rows.createCell(i++).setCellValue(xlxsEntity.getOilwellLow());
        rows.createCell(i++).setCellValue(xlxsEntity.getOilwellDeep());
        rows.createCell(i++).setCellValue(xlxsEntity.getPosition());
        rows.createCell(i++).setCellValue(xlxsEntity.getLithology());
        rows.createCell(i++).setCellValue(xlxsEntity.getFluidName());
        rows.createCell(i++).setCellValue(xlxsEntity.getNotes());*/
        int index = i;
        for (int j = 0; j < XlxsEntity.getInformNum(); j++, index++) {
            Field declaredField = declaredFields[index];

            declaredField.setAccessible(true);
            try {
                InformClassify o = (InformClassify) declaredField.get(xlxsEntity);
                for (int z = 0; z < o.getSize(); z++) {
                    rows.createCell(i++).setCellValue(o.getData().get(z));
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }


        }

         /*       for (int j = 0; j < xlxsEntity.getHeavyMetal().getData().size(); j++) {

              rows.createCell(i++).setCellValue(xlxsEntity.getHeavyMetal().getData().get(j));
                }







            for (int j = 0; j < xlxsEntity.getNonHeavyMetal().getData().size(); j++) {

                rows.createCell(i++).setCellValue(xlxsEntity.getNonHeavyMetal().getData().get(j));
            }







            for (int j = 0; j < xlxsEntity.getMineral().getData().size(); j++) {

                rows.createCell(i++).setCellValue(xlxsEntity.getMineral().getData().get(j));
            }
*/


    }


    private void createFirstRow(int columnNum, List<InformClassify> column, XSSFRow row) {
        List<String> detailList = getDetailList();

        for (int j = 0; j < column.size(); j++) {
            int z = 0;
            while (!(detailList.get(j).equals(column.get(z).getName()))) {
                z++;

            }
            InformClassify informNow = column.get(z);
            for (int i = 0; i < informNow.getSize(); i++) {
                row.createCell(columnNum++).setCellValue(informNow.getColums().get(i) + "含量");


            }
        }

        System.out.println("12");
    }


    private int creatfirstRowConstant(XSSFRow row) {
        int cellNumber = 0;
        row.createCell(0).setCellValue("类型");
        row.createCell(1).setCellValue("id");
        row.createCell(2).setCellValue("样本名");
        row.createCell(3).setCellValue("区块名");
        row.createCell(4).setCellValue("油井名");
        row.createCell(5).setCellValue("井深低值");
        row.createCell(6).setCellValue("井深高值");
        row.createCell(7).setCellValue("层位");
        row.createCell(8).setCellValue("岩性");
        row.createCell(9).setCellValue("钻井液名");
        row.createCell(10).setCellValue("备注");

        return cellNumber = 11;


    }


    private List<InformClassify> queryFromDictionary() {
        if (queryClassifyBackUp != null)
            return queryClassifyBackUp;
        List<String> detailInform = getDetailList();
        List<InformClassify> informClassifies = new ArrayList<>();

        for (int i = 0; i < 3; i++) {
            InformClassify informClassify = new InformClassify();
            String informName = detailInform.get(i);

            DataDictionary dataDictionary = dictionaryName.lambdaQuery().select(DataDictionary::getDictId).eq(DataDictionary::getDictName, informName).one();
            String id = dataDictionary.getDictId();

            List<DictionaryContent> list1 = dictionaryContent.lambdaQuery().select(DictionaryContent::getContentName).eq(DictionaryContent::getDictId, id).list();
            List<String> names = list1.stream().map(DictionaryContent::getContentName).collect(Collectors.toList());
            informClassify.setNameAndClass(informName);
            informClassify.setColums(names);
            informClassifies.add(informClassify);

        }
        queryClassifyBackUp = informClassifies;
        return informClassifies;

    }

    private List<String> getDetailList() {
        ArrayList<String> strings = new ArrayList<>();

        Class<XlxsEntity> xlxsEntityClass = XlxsEntity.class;
        Field[] declaredFields = xlxsEntityClass.getDeclaredFields();
        int index = XlxsEntity.getInformOrder();
        int times = XlxsEntity.getInformNum();
        for (int i = 0; i < times; i++, index++) {
            Field declaredField = declaredFields[index];
            declaredField.setAccessible(true);
            try {
                InformClassify o = (InformClassify) declaredField.get(forDetailName);

                strings.add(o.getName());
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }


        }

        return strings;


    }
  /*  public static List<User> read(String path) throws IOException, ParseException {
        List<User> userList = new ArrayList<>();

        // 1. 获取工作簿
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
        // 2. 获取工作表
        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        // 获取实际的行数，不包括空行
        int totalRows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < totalRows; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row != null) {
                ArrayList<String> list = new ArrayList<>();
                for (Cell cell : row) {
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);

                        String value = cell.getStringCellValue();// 读取数据
                        System.out.println("单元格值: '" + value + "'");

                        // 检查单元格值是否为空
                        if (value != null && !value.trim().isEmpty()) {
                            list.add(value);
                        }
                    }
                }

                // 检查是否有足够的列数
                if (list.size() >= 6) {
                    String dateString = list.get(4);
                    String dateString2 = list.get(5);

                    // 处理 Excel 序列数转换为日期
                    double excelDate1 = Double.parseDouble(dateString);
                    double excelDate2 = Double.parseDouble(dateString2);

                    // 转换 Excel 日期为 Java 日期
                    Date parse = convertExcelDateToJavaDate(excelDate1);
                    Date parse2 = convertExcelDateToJavaDate(excelDate2);

                    // 创建 User 对象并添加到列表
                    User user = new User(Long.parseLong(list.get(0)), list.get(1), Integer.parseInt(list.get(2)),
                            list.get(3), parse, parse2);
                    userList.add(user);
                }
            }
        }
        return userList;
    }*/

    public XlxsEntity initXlxsEntity(String id, XlxsDataType dataType) {
        XlxsEntity xlxsEntity = null;
        switch (dataType) {
            case  NATIVE:
                Native one = null;
                one = iNativeService.lambdaQuery().eq(Native::getId, id).one();
                xlxsEntity = BeanUtil.copyProperties(one, XlxsEntity.class);
                break;

            case SECONDARY:
                Secondary one1 = null;
                one1 = iSecondaryService.lambdaQuery().eq(Secondary::getId, id).one();
                xlxsEntity = BeanUtil.copyProperties(one1, XlxsEntity.class);
                break;

        }

        List<InformClassify> informClassifies = queryFromDictionary();
        xlxsEntity.setAll(informClassifies);
       xlxsEntity.setType(dataType.getTypeName());

        return xlxsEntity;
    }


    public List<XlxsEntity> queryData(List<String> ids,XlxsDataType xlxsDataType) {
        List<XlxsEntity> xlxsEntities = new ArrayList<>();

        for (int i = 0; i < ids.size(); i++) {
            String id = ids.get(i);
            XlxsEntity xlxsEntity = initXlxsEntity(id,xlxsDataType);


            List<Inform> informHeavy = iHeavyMetalService.lambdaQuery().select(HeavyMetal::getName, HeavyMetal::getData)
                    .eq(HeavyMetal::getBelong, id).list().stream().map(heavyMetal -> (Inform) heavyMetal).collect(Collectors.toList());
            List<Inform> informsNonHeavy = iNonHeavyMetalService.lambdaQuery().select(NonHeavyMetal::getName, NonHeavyMetal::getData)
                    .eq(NonHeavyMetal::getBelong, id).list().stream().map(nonHeavyMetal -> (Inform) nonHeavyMetal).collect(Collectors.toList());
            List<Inform> informsMineral = iMineralService.lambdaQuery().select(Mineral::getName, Mineral::getData)
                    .eq(Mineral::getBelong, id).list().stream().map(mineral -> (Inform) mineral).collect(Collectors.toList());

            xlxsEntity.initInformData(XlxsConstant.INFORM_HEAVY, informHeavy);
            xlxsEntity.initInformData(XlxsConstant.INFORM_NON_HEAVY, informsNonHeavy);
            xlxsEntity.initInformData(XlxsConstant.INFORM_MINERAL, informsMineral);


            xlxsEntities.add(xlxsEntity);


        }

        System.out.println(1);
        return xlxsEntities;

    }


}
